﻿CREATE PROCEDURE [dbo].[proc_OrderId_Getlist]
(
	@CompanyId int,
	@DepId int,
	@StateId int,
	@TypeId int,
	@WayId int,
	@ProcessId int,
	@Stext nvarchar(50),
	@StartDate varchar(20),
	@EndDate varchar(20),
	@StartIndex int,
	@EndIndex int
)
AS
Begin
	Declare @bmbh_T  varchar(30)
	Select @bmbh_T=bmbh From department Where id=@DepId

	;WITH list As(Select ROW_NUMBER() OVER (Order By OrderId Desc,id Desc) As Row
		,Id
		,OId
		,OrderId
		,CusId
		,CusName
		,CusPerson
		,CusPersonTel
		,CusDescription
		,CusEstimate
		,TypeId
		,TypeName
		,WayId
		,WayName
		,ProcessId
		,ProcessName
		,Salesman
		,SalesmanAccount
		,TradeDate
		,CreateDate
		,YingShouPrice
		,IsNull(ShiShouPrice,0) As ShiShouPrice
		,Discount
		,PaymentTypeId
		,PaymentType
		,BankId
		,Bank
		,PaymentDescription
		,OtherDescription
		,DepId
		,CompanyId
		,StateId
		,IsGreen
		,IsNull((Select Title From Order_State Where Id=[Order].StateId),'---') As StateName
		From [Order]
		Where (@CompanyId=0 Or CompanyId=@CompanyId) And (@DepId=0 Or DepId In(Select Id From department Where Left(bmbh,Len(@bmbh_T))=@bmbh_T))
		And StateId>1 and (@TypeId=0 Or TypeId=@TypeId) And (@WayId=0 Or WayId=@WayId) And (@ProcessId=0 Or ProcessId=@ProcessId)
--		And (Convert(nchar(10),FinanceRenlingDate,120) between @StartDate and @EndDate)
		And (@Stext='' Or CusName like '%'+@Stext+'%' Or Salesman like '%'+@Stext+'%')
		And Left(OrderId,8)>=Replace(@StartDate,'-','') And Left(OrderId,8)<=Replace(@EndDate,'-','')
--		And (Left(OrderId,8)=Convert(varchar(10),FinanceRenlingDate,112) OR Left(OrderId,8)=Convert(varchar(10), DATEADD(dd,-1, FinanceRenlingDate),112))
	)
	
	Select *,(Select Count(0) From list) As RecordCount
	,(select sum(YingShouPrice) from list) as DataSum
	 From list Where Row Between @StartIndex and @EndIndex Order By Row
End
